package com.project.module.utill;



import com.project.module.exception.CommonException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 操作Excel表的工具类
 */
public class ExcelUtil {
    //限制单张sheet表中存储的最大数据数量
    private static Integer MAX_SHEET_SIZE = 5000;

    /**
     * 数据导出（.xls格式）
     *
     * @param data   数据  list
     * @param out    输出流
     * @param fields 对应的属性 属性键为  javaBean的属性    值为中文字段
     * @param <T>
     * @throws Exception
     */
    public static <T> void list2Excel(List<T> data, OutputStream out, LinkedHashMap<String, String> fields) throws Exception {
        //【1】参数验证
        if (data == null || data.size() == 0) {
            throw new Exception("【错误信息】：导出数据为空！");
        }
        //【2】创建工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();

        //【3】计算创建多少个sheet表格(向上取整)
        int sheetNumber = (int) Math.ceil((double) data.size() / MAX_SHEET_SIZE);

        //【4】解析map集合到Excel中
        String[] fieldNames = new String[fields.size()];
        String[] chinaNames = new String[fields.size()];
        int count = 0;
        for (Map.Entry<String, String> entry : fields.entrySet()) {
            fieldNames[count] = entry.getKey();
            chinaNames[count] = entry.getValue();
            count++;
        }
        //【创建表格】 循环创建
        for (int i = 0; i < sheetNumber; i++) {
            int rowCount = 0; //每一张表从第一行开始填充
            //创建表 和 行
            HSSFSheet sheet = workbook.createSheet();
            HSSFRow row = sheet.createRow(rowCount);

            //设置单元格格式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            for (int j = 0; j < fields.size(); j++) {
                HSSFCell cell = row.createCell(j);
                //标题行
                cell.setCellValue(chinaNames[j]);
                cell.setCellStyle(cellStyle);
            }
            rowCount++;
            int startIndex = i * MAX_SHEET_SIZE;
            int endIndex = (i + 1) * MAX_SHEET_SIZE - 1;
            if (endIndex > data.size()) {
                endIndex = data.size();
            }
            //为当前的sheet表写入数据
            for (int index = startIndex; index < endIndex; index++) {
                //获得集合当前指定行的数据
                T item = data.get(index);
                //从第二行开始创建 ，并且为每一行创建对应数量的单元格
                row = sheet.createRow(rowCount);
                for (int j = 0; j < fields.size(); j++) {
                    //利用反射获取字段的值  赋予单元格
                    Field field = item.getClass().getDeclaredField(fieldNames[j]);
                    field.setAccessible(true);
                    Object object = field.get(item);
                    String value = (object == null ? "" : object.toString());
                    row.createCell(j).setCellValue(value);
                }
                rowCount++;
            }
            workbook.write(out);
        }


    }

   /* *//**
     * 导入物料详情单
     *
     * @param file
     * @return
     *//*
    public static List<MRLine> importMrLines(MultipartFile file) {
        if (file.isEmpty()) {
            throw new CommonException("【错误信息】：导入文件不能为空！");
        }
        List list = new ArrayList();
        String extName = file.getOriginalFilename()
                .substring(file.getOriginalFilename()
                        .lastIndexOf("."));
        Workbook a_workbook = null;
        InputStream in = null;
        try {
            in = file.getInputStream();

            if (".xlsx".equals(extName)) {
                a_workbook = new XSSFWorkbook(in);
                list = importMrline2007(a_workbook);
            } else if (".xls".equals(extName)) {
                a_workbook = new HSSFWorkbook(in);
                list = importMrline2003(a_workbook);
            } else {
                throw new CommonException("文件格式不对！");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return list;
    }


    *//**
     * 导入物料详情列表  2017版的
     *
     * @param a_workbook
     * @return
     *//*
    private static List<MRLine> importMrline2007(Workbook a_workbook) {
        XSSFWorkbook workbook = (XSSFWorkbook) a_workbook;
        List list = new ArrayList();
        Sheet sheet = workbook.getSheetAt(0);
        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        //第三行进行遍历
        if (maxRow < 1) {
            throw new CommonException("表格中至少要有一行有效数据！");
        }

        for (int i = 1; i <= maxRow; i++) {
            Row row = sheet.getRow(i);

            MRLine mrLine = new MRLine();
            // 物料小类
            if (row.getCell(0) != null) {
                String smallClassCode = row.getCell(0).toString();
                mrLine.setSmallClassCode(smallClassCode);
            }
            //物料名称
            if (row.getCell(1) == null||"".equals(row.getCell(1).toString())) {
                throw new CommonException("【错误信息】：第" + i + 1 + "行，物料名称不能为空！");
            }
            mrLine.setItemDesc(row.getCell(1).toString());
            //计量单位
            if (row.getCell(2) != null) {
                String itemUnit = row.getCell(2).toString();
                mrLine.setItemUnit(itemUnit);
            }
            //模板
            if (row.getCell(3) != null) {
                String applyTemplate = row.getCell(3).toString();
                mrLine.setApplyTemplate(applyTemplate);
            }


            list.add(mrLine);
        }


        return list;
    }

    *//**
     * 导入物料详情列表 2013版的
     *
     * @param a_workbook
     * @return
     *//*
    private static List<MRLine> importMrline2003(Workbook a_workbook) {
        HSSFWorkbook workbook = (HSSFWorkbook) a_workbook;
        List list = new ArrayList();
        HSSFSheet sheet = workbook.getSheetAt(0);
        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        if (maxRow < 1) {
            throw new CommonException("表格中至少要有一行有效数据！");
        }
        //第二行进行遍历
        for (int i = 1; i <= maxRow; i++) {
            HSSFRow row = sheet.getRow(i);
            MRLine mrLine = new MRLine();
            // 物料小类
            if (row.getCell(0) != null) {
                String smallClassCode = row.getCell(0).toString();
                mrLine.setSmallClassCode(smallClassCode);
            }
            //物料名称
            if (row.getCell(1) == null||"".equals(row.getCell(1).toString())) {
                throw new CommonException("【错误信息】：第" + i + 1 + "行，物料名称不能为空！");
            }
            mrLine.setItemDesc(row.getCell(1).toString());
            //计量单位
            if (row.getCell(2) != null) {
                String itemUnit = row.getCell(2).toString();
                mrLine.setItemUnit(itemUnit);
            }
            //模板
            if (row.getCell(3) != null) {
                String applyTemplate = row.getCell(3).toString();
                mrLine.setApplyTemplate(applyTemplate);
            }


            list.add(mrLine);

        }
        return list;

    }
*/

    /**
     * 通用版导入Excel 只能导入2003 返回
     *
     * @param properties
     * @param clazz
     * @param in
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(Map<String, String> properties, Class<T> clazz, InputStream in) throws Exception {

        //参数合法性验证
        if (properties == null || clazz == null || in == null) {
            throw new RuntimeException("参数不能为空！");
        }
        Workbook workbook = null;
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);

        }
//2003板
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            workbook = new HSSFWorkbook(in);
        }


        List collection = new ArrayList();
        //获取属性值
        Field[] fields = clazz.getDeclaredFields();
        //创建工作薄
        // HSSFWorkbook workbook = new HSSFWorkbook(in);
        //默认获取第一个sheet表
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        //从索引 0 主题
        //从索引 1 开始读取字段
        //从索引 >=2 开始读取数据

        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        //获取第二行的字段
        HSSFRow topic_row = sheet.getRow(1);

        int column = topic_row.getLastCellNum();
/*
        for (int j = 0; j < column; j++) {
            String top = topic_row.getCell(j).getStringCellValue();
            System.out.println("字段名称：" + top);
        }*/
        //从第三行开始赋值
        for (int i = 2; i <= maxRow; i++) {
            //获取字段
            //获取第一个字段
            HSSFRow value_row = sheet.getRow(i);
            //从第三行获取  字段值
            T obj = clazz.newInstance();
            for (int j = 0; j < column; j++) {
                //从第一列单元格 ,开始赋值
                HSSFCell cell = value_row.getCell(j);
                String data = "";
                if (cell != null) {
                    data = value_row.getCell(j).toString();
                }

                if (data.trim().length() == 0) {
                    throw new RuntimeException("kong");
                }
                //获取第一列的字段；判断字段和
                String key_fields = topic_row.getCell(j).toString();

                for (Map.Entry<String, String> entry : properties.entrySet()) {
                    //如果当前的字段=map中的键 ，把值对应的属性 赋值
                    if (key_fields.equals(entry.getKey())) {
                        //获取当前列的对应的java字段
                        Field field = clazz.getDeclaredField(entry.getValue());
                        //str.substring(0, 1).toUpperCase() + str.substring(1);
                        String methodName = "set" + (entry.getValue()).substring(0, 1).toUpperCase() + (entry.getValue()).substring(1);
                        Method method;
                        try {
                            //获取该字段的类型
                            System.out.println("mth:" + field.getType().getName());
                            // 这部分可自己扩展
                            if (field.getType().getName().equals("java.lang.String")) {
                                method = clazz.getDeclaredMethod(methodName,
                                        String.class); // 设置要执行的方法--set方法参数为String
                                method.invoke(obj, data); // 执行该方法
                            } else if (field.getType().getName().equals("java.lang.Integer")) {
                                method = clazz.getDeclaredMethod(methodName,
                                        Integer.class); // 设置要执行的方法--set方法参数为int
                                Integer data_int = Integer.valueOf(data);
                                method.invoke(obj, data_int); // 执行该方法
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        break;
                    }
                }
            }
            collection.add(obj);
        }
        return collection;
    }


    ////////////////////////////////////////////////////////////////////////

    /**
     * @param properties
     * @param clazz
     * @param in
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcels(Map<String, String> properties, Class<T> clazz, InputStream in) throws Exception {

        //参数合法性验证
        if (properties == null || clazz == null || in == null) {
            throw new RuntimeException("参数不能为空！");
        }
        Workbook workbook = null;
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);

        }
//2003板
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            workbook = new HSSFWorkbook(in);
        }


        List collection = new ArrayList();
        //获取属性值
        Field[] fields = clazz.getDeclaredFields();
        //创建工作薄
        // HSSFWorkbook workbook = new HSSFWorkbook(in);
        //默认获取第一个sheet表
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);

        //从索引 0 开始读取字段
        //从索引 >=1 开始读取数据

        //获取最大的行数
        int maxRow = sheet.getLastRowNum();
        //获取第一行（包含字段信息）
        HSSFRow topic_row = sheet.getRow(0);
        //获取最后一行的信息
        int column = topic_row.getLastCellNum();
/*
        for (int j = 0; j < column; j++) {
            String top = topic_row.getCell(j).getStringCellValue();
            System.out.println("字段名称：" + top);
        }*/
        //从第二行开始赋值
        for (int i = 1; i <= maxRow; i++) {
            //获取字段
            //获取第一个字段
            HSSFRow value_row = sheet.getRow(i);
            //从第三行获取  字段值
            T obj = clazz.newInstance();
            for (int j = 0; j < column; j++) {
                //从第一列单元格 ,开始赋值
                HSSFCell cell = value_row.getCell(j);
                String data = "";
                if (cell != null) {
                    data = value_row.getCell(j).toString();
                }

                if (data.trim().length() == 0) {
                    throw new RuntimeException("kong");
                }
                //获取第一列的字段；判断字段和
                String key_fields = topic_row.getCell(j).toString();

                for (Map.Entry<String, String> entry : properties.entrySet()) {
                    //如果当前的字段=map中的键 ，把值对应的属性 赋值
                    if (key_fields.equals(entry.getKey())) {
                        //获取当前列的对应的java字段
                        Field field = clazz.getDeclaredField(entry.getValue());
                        //str.substring(0, 1).toUpperCase() + str.substring(1);
                        String methodName = "set" + (entry.getValue()).substring(0, 1).toUpperCase() + (entry.getValue()).substring(1);
                        Method method;
                        try {
                            //获取该字段的类型
                            System.out.println("mth:" + field.getType().getName());
                            // 这部分可自己扩展
                            if (field.getType().getName().equals("java.lang.String")) {
                                method = clazz.getDeclaredMethod(methodName,
                                        String.class); // 设置要执行的方法--set方法参数为String
                                method.invoke(obj, data); // 执行该方法
                            } else if (field.getType().getName().equals("java.lang.Integer")) {
                                method = clazz.getDeclaredMethod(methodName,
                                        Integer.class); // 设置要执行的方法--set方法参数为int
                                Integer data_int = Integer.valueOf(data);
                                method.invoke(obj, data_int); // 执行该方法
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        break;
                    }
                }
            }
            collection.add(obj);
        }
        return collection;
    }


}
